using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public TicketfinVO getTicketfin(int? idTicketFin, string origem)
		{
			TicketfinVO ticketfinVO = null;
			List<TicketfinVO> listTicketfinVOs = getTicketfins(idTicketFin, origem, null, null, null, null, null, null, null);
			if (listTicketfinVOs != null && listTicketfinVOs.Count > 0)
			{
				ticketfinVO = listTicketfinVOs[0];
			}
			return ticketfinVO;
		}

		public TicketfinVO getTicketfin(int? idTicketFin)
		{
			return getTicketfin(idTicketFin, null);
		}

		public List<TicketfinVO> getTicketfins()
		{
			return getTicketfins(null, null, null, null, null, null, null, null, null);
		}

		public List<TicketfinVO> getTicketfins(int? idTicketFin)
		{
			return getTicketfins(idTicketFin, null, null, null, null, null, null, null, null);
		}

		public List<TicketfinVO> getTicketfins(int? idTicketFin, string origem)
		{
			return getTicketfins(idTicketFin, origem, null, null, null, null, null, null, null);
		}

        public List<TicketfinVO> getTicketfins(int? idTicketFin, string origem, int? idFilial, int? idCaixaMovto, int? idCaixaMovtoA, string finalidade, char? situacao)
        {
            return getTicketfins(idTicketFin, origem, idFilial, idCaixaMovto, idCaixaMovtoA, finalidade, situacao, null);
        }

        public List<TicketfinVO> getTicketfins(int? idTicketFin, string origem, int? idFilial, int? idCaixaMovto, int? idCaixaMovtoA, string finalidade, char? situacao, DateTime? dtTicketFinDe)
        {
            return getTicketfins(idTicketFin, origem, idFilial, idCaixaMovto, idCaixaMovtoA, finalidade, situacao, dtTicketFinDe, null);
        }

        public List<TicketfinVO> getTicketfins(int? idTicketFin, string origem, int? idFilial, int? idCaixaMovto, int? idCaixaMovtoA, string finalidade, char? situacao, DateTime? dtTicketFinDe, DateTime? dtTicketFinAte)
        {
            return getTicketfins(idTicketFin, origem, idFilial, idCaixaMovto, idCaixaMovtoA, finalidade, situacao, dtTicketFinDe, dtTicketFinAte, null);
        }

        public List<TicketfinVO> getTicketfins(int? idTicketFin, string origem, int? idFilial, int? idCaixaMovto, int? idCaixaMovtoA, string finalidade, char? situacao, DateTime? dtTicketFinDe, DateTime? dtTicketFinAte, DateTime? dtAcolhimentoDe)
        {
            return getTicketfins(idTicketFin, origem, idFilial, idCaixaMovto, idCaixaMovtoA, finalidade, situacao, dtTicketFinDe, dtTicketFinAte, dtAcolhimentoDe, null);
        }

        public List<TicketfinVO> getTicketfins(int? idTicketFin, string origem, int? idFilial, int? idCaixaMovto, int? idCaixaMovtoA, string finalidade, char? situacao, DateTime? dtTicketFinDe, DateTime? dtTicketFinAte, DateTime? dtAcolhimentoDe, DateTime? dtAcolhimentoAte)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT tkf.idTicketFin as tkf_idTicketFin, tkf.dtTicketFin as tkf_dtTicketFin, tkf.origem as tkf_origem, tkf.finalidade as tkf_finalidade, tkf.situacao as tkf_situacao, tkf.valor as tkf_valor, tkf.dtAcolhimento as tkf_dtAcolhimento, tkf.idFilial as tkf_idFilial, fil.filial as fil_filial, tkf.idCaixaMvto as tkf_idCaixaMvto, cxm.mvto as cxm_mvto, tkf.idCaixaMvtoA as tkf_idCaixaMvtoA, cxm2.mvto as cxm2_mvto, tkf.idMvtoFin as tkf_idMvtoFin, tkf.idMvtoFinA as tkf_idMvtoFinA, tkf.cadastro as tkf_cadastro, tkf.cadastradoPor as tkf_cadastradoPor, tkf.edicao as tkf_edicao, tkf.editadoPor as tkf_editadoPor  ");
			query.AppendLine("FROM ticketfin as tkf LEFT OUTER JOIN filial as fil on tkf.idFilial=fil.idFilial  LEFT OUTER JOIN caixamvto as cxm on tkf.idCaixaMvto=cxm.idCaixaMvto  LEFT OUTER JOIN caixamvto as cxm2 on tkf.idCaixaMvtoA=cxm2.idCaixaMvto  ");
			if (idTicketFin != null)
			{
				query.AppendLine(where + "tkf.idTicketFin=@idTicketFin ");
				where = "AND ";
			}
			if (origem != null)
			{
				query.AppendLine(where + "tkf.origem=@origem ");
				where = "AND ";
			}
			if (finalidade != null)
			{
				query.AppendLine(where + "tkf.finalidade=@finalidade ");
				where = "AND ";
			}
			if (situacao != null)
			{
				query.AppendLine(where + "tkf.situacao=@situacao ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "tkf.idFilial=@idFilial ");
				where = "AND ";
			}
			if (idCaixaMovto != null)
			{
				query.AppendLine(where + "tkf.idCaixaMovto=@idCaixaMovto ");
				where = "AND ";
			}
			if (idCaixaMovtoA != null)
			{
				query.AppendLine(where + "tkf.idCaixaMovtoA=@idCaixaMovtoA ");
				where = "AND ";
			}
            if (dtTicketFinDe != null)
            {
                query.AppendLine(where + "tkf.dtTicketFin>=@dtTicketFinDe ");
                where = "AND ";
            }
            if (dtTicketFinAte != null)
            {
                query.AppendLine(where + "tkf.dtTicketFin<=@dtTicketFinAte ");
                where = "AND ";
            }
            if (dtAcolhimentoDe != null)
            {
                query.AppendLine(where + "tkf.dtAcolhimento>=@dtAcolhimentoDe ");
                where = "AND ";
            }
            if (dtAcolhimentoAte != null)
            {
                query.AppendLine(where + "tkf.dtAcolhimento<=@dtAcolhimentoAte ");
                where = "AND ";
            }




            query.AppendLine("ORDER BY tkf.dtTicketFin ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idTicketFin!= null) cmd.Parameters.Add(getParameter(cmd, "@idTicketFin", idTicketFin));
			if (origem!= null) cmd.Parameters.Add(getParameter(cmd, "@origem", origem));
			if (finalidade!= null) cmd.Parameters.Add(getParameter(cmd, "@finalidade", finalidade));
			if (situacao!= null) cmd.Parameters.Add(getParameter(cmd, "@situacao", situacao));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (idCaixaMovto!= null) cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovto", idCaixaMovto));
			if (idCaixaMovtoA!= null) cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovtoA", idCaixaMovtoA));

            if (dtTicketFinDe != null)
            {
                DateTime data = new DateTime(dtTicketFinDe.Value.Year, dtTicketFinDe.Value.Month, dtTicketFinDe.Value.Day, 0, 0, 0);
                cmd.Parameters.Add(getParameter(cmd, "@dtTicketFinDe", data));
            }

            if (dtTicketFinAte != null)
            {
                DateTime data = new DateTime(dtTicketFinAte.Value.Year, dtTicketFinAte.Value.Month, dtTicketFinAte.Value.Day, 23, 59, 59);
                cmd.Parameters.Add(getParameter(cmd, "@dtTicketFinAte", data));
            }

            if (dtAcolhimentoDe != null)
            {
                DateTime data = new DateTime(dtAcolhimentoDe.Value.Year, dtAcolhimentoDe.Value.Month, dtAcolhimentoDe.Value.Day, 0, 0, 0);
                cmd.Parameters.Add(getParameter(cmd, "@dtAcolhimentoDe", data));
            }

            if (dtAcolhimentoAte != null)
            {
                DateTime data = new DateTime(dtAcolhimentoAte.Value.Year, dtAcolhimentoAte.Value.Month, dtAcolhimentoAte.Value.Day, 23, 59, 59);
                cmd.Parameters.Add(getParameter(cmd, "@dtAcolhimentoAte", data));
            }

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<TicketfinVO> listTicketfin = new List<TicketfinVO>();

				while (reader.Read())
				{
					TicketfinVO ticketfinVO = TicketfinVO.CreateVO();
					ticketfinVO.idTicketFin = reader.GetInt32(reader.GetOrdinal("tkf_idTicketFin"));
					ticketfinVO.dtTicketFin = reader.GetDateTime(reader.GetOrdinal("tkf_dtTicketFin"));
					ticketfinVO.origem = reader.GetString(reader.GetOrdinal("tkf_origem"));
					if (!reader.IsDBNull(reader.GetOrdinal("tkf_finalidade")))
						ticketfinVO.finalidade = reader.GetString(reader.GetOrdinal("tkf_finalidade"));
					ticketfinVO.situacao = reader.GetChar(reader.GetOrdinal("tkf_situacao"));
					ticketfinVO.valor = reader.GetDecimal(reader.GetOrdinal("tkf_valor"));
					if (!reader.IsDBNull(reader.GetOrdinal("tkf_dtAcolhimento")))
						ticketfinVO.dtAcolhimento = reader.GetDateTime(reader.GetOrdinal("tkf_dtAcolhimento"));
					ticketfinVO.idFilial = reader.GetInt32(reader.GetOrdinal("tkf_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						ticketfinVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
					if (!reader.IsDBNull(reader.GetOrdinal("tkf_idCaixaMvto")))
						ticketfinVO.idCaixaMovto = reader.GetInt32(reader.GetOrdinal("tkf_idCaixaMvto"));
					if (!reader.IsDBNull(reader.GetOrdinal("cxm_mvto")))
						ticketfinVO.idCaixaMovtoVO.mvto = reader.GetString(reader.GetOrdinal("cxm_mvto"));
					if (!reader.IsDBNull(reader.GetOrdinal("tkf_idCaixaMvtoA")))
						ticketfinVO.idCaixaMovtoA = reader.GetInt32(reader.GetOrdinal("tkf_idCaixaMvtoA"));
					if (!reader.IsDBNull(reader.GetOrdinal("cxm2_mvto")))
						ticketfinVO.idCaixaMovtoAVO.mvto = reader.GetString(reader.GetOrdinal("cxm2_mvto"));
					if (!reader.IsDBNull(reader.GetOrdinal("tkf_idMvtoFin")))
						ticketfinVO.idMvtoFin = reader.GetInt32(reader.GetOrdinal("tkf_idMvtoFin"));
					if (!reader.IsDBNull(reader.GetOrdinal("tkf_idMvtoFinA")))
						ticketfinVO.idMvtoFinA = reader.GetInt32(reader.GetOrdinal("tkf_idMvtoFinA"));
					ticketfinVO.cadastro = reader.GetDateTime(reader.GetOrdinal("tkf_cadastro"));
					ticketfinVO.cadastradoPor = reader.GetString(reader.GetOrdinal("tkf_cadastradoPor"));
					ticketfinVO.edicao = reader.GetDateTime(reader.GetOrdinal("tkf_edicao"));
					ticketfinVO.editadoPor = reader.GetString(reader.GetOrdinal("tkf_editadoPor"));


					listTicketfin.Add(ticketfinVO);
				}
				reader.Close();
				return listTicketfin;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setTicketfin(TicketfinVO ticketfinVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE ticketfin ");
			query.AppendLine("SET dtTicketFin=@dtTicketFin, origem=@origem, finalidade=@finalidade, situacao=@situacao, valor=@valor, dtAcolhimento=@dtAcolhimento, idFilial=@idFilial, idCaixaMvto=@idCaixaMovto, idCaixaMvtoA=@idCaixaMovtoA, idMvtoFin=@idMvtoFin, idMvtoFinA=@idMvtoFinA,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idTicketFin=@idTicketFin ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idTicketFin", ticketfinVO.idTicketFin));
			cmd.Parameters.Add(getParameter(cmd, "@dtTicketFin", ticketfinVO.dtTicketFin));
			cmd.Parameters.Add(getParameter(cmd, "@origem", ticketfinVO.origem));
			if (ticketfinVO.finalidade == null)
				cmd.Parameters.Add(getParameter(cmd, "@finalidade", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@finalidade", ticketfinVO.finalidade));
			cmd.Parameters.Add(getParameter(cmd, "@situacao", ticketfinVO.situacao));
			cmd.Parameters.Add(getParameter(cmd, "@valor", ticketfinVO.valor));
			if (ticketfinVO.dtAcolhimento == null)
				cmd.Parameters.Add(getParameter(cmd, "@dtAcolhimento", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@dtAcolhimento", ticketfinVO.dtAcolhimento));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", ticketfinVO.idFilial));
			if (ticketfinVO.idCaixaMovto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovto", ticketfinVO.idCaixaMovto));
			if (ticketfinVO.idCaixaMovtoA == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovtoA", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovtoA", ticketfinVO.idCaixaMovtoA));
			if (ticketfinVO.idMvtoFin == null)
				cmd.Parameters.Add(getParameter(cmd, "@idMvtoFin", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idMvtoFin", ticketfinVO.idMvtoFin));
			if (ticketfinVO.idMvtoFinA == null)
				cmd.Parameters.Add(getParameter(cmd, "@idMvtoFinA", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idMvtoFinA", ticketfinVO.idMvtoFinA));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", ticketfinVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", ticketfinVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", ticketfinVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", ticketfinVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newTicketfin(TicketfinVO ticketfinVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO ticketfin ");
			query.AppendLine("  ( dtTicketFin, origem, finalidade, situacao, valor, dtAcolhimento, idFilial, idCaixaMvto, idCaixaMvtoA, idMvtoFin, idMvtoFinA, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @dtTicketFin, @origem, @finalidade, @situacao, @valor, @dtAcolhimento, @idFilial, @idCaixaMovto, @idCaixaMovtoA, @idMvtoFin, @idMvtoFinA, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@dtTicketFin", ticketfinVO.dtTicketFin));
			cmd.Parameters.Add(getParameter(cmd, "@origem", ticketfinVO.origem));
			if (ticketfinVO.finalidade == null)
				cmd.Parameters.Add(getParameter(cmd, "@finalidade", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@finalidade", ticketfinVO.finalidade));
			cmd.Parameters.Add(getParameter(cmd, "@situacao", ticketfinVO.situacao));
			cmd.Parameters.Add(getParameter(cmd, "@valor", ticketfinVO.valor));
			if (ticketfinVO.dtAcolhimento == null)
				cmd.Parameters.Add(getParameter(cmd, "@dtAcolhimento", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@dtAcolhimento", ticketfinVO.dtAcolhimento));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", ticketfinVO.idFilial));
			if (ticketfinVO.idCaixaMovto == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovto", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovto", ticketfinVO.idCaixaMovto));
			if (ticketfinVO.idCaixaMovtoA == null)
				cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovtoA", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idCaixaMovtoA", ticketfinVO.idCaixaMovtoA));
			if (ticketfinVO.idMvtoFin == null)
				cmd.Parameters.Add(getParameter(cmd, "@idMvtoFin", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idMvtoFin", ticketfinVO.idMvtoFin));
			if (ticketfinVO.idMvtoFinA == null)
				cmd.Parameters.Add(getParameter(cmd, "@idMvtoFinA", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idMvtoFinA", ticketfinVO.idMvtoFinA));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", ticketfinVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", ticketfinVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", ticketfinVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", ticketfinVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('ticketfin') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delTicketfin(TicketfinVO ticketfinVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM ticketfin ");
			query.AppendLine("WHERE idTicketFin=@idTicketFin ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idTicketFin", ticketfinVO.idTicketFin));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountTicketfinxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM ticketfin WHERE IdFilial=@IdFilial ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountTicketfinxIdCaixaMovto(int? idCaixaMovto)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCaixaMovto) as ocorrencias FROM ticketfin WHERE IdCaixaMovto=@IdCaixaMovto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCaixaMovto", idCaixaMovto));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountTicketfinxIdCaixaMovtoA(int? idCaixaMovtoA)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCaixaMovtoA) as ocorrencias FROM ticketfin WHERE IdCaixaMovtoA=@IdCaixaMovtoA ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCaixaMovtoA", idCaixaMovtoA));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
